#include "serveradmin.h"

#include <sqlite3.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#include "StdTcp.h"
#include "DoubleLinkList.h"
//12 注册账户
void ServerAdminRegister(Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    char sql[200];
    sprintf(sql,"insert into users(myname) values('%s');",m->myname);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        
        sqlite3_close(db);
        return;
    }
    memset(sql,0,sizeof(sql));
    strcpy(sql,"select serial from users;");
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(result[row]==NULL)
    {
        sqlite3_close(db);
        return;                
    }
    int num=atoi(result[row]);
    sqlite3_free_table(result);
    m->mynameid=num+10000;
    memset(sql,0,200);
    sprintf(sql,"update users set password='%s' where serial=%d;",m->password,num);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(sql,0,200);
    sprintf(sql,"update users set mynameid=%d where serial=%d;",m->mynameid,num);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(sql,0,200);
    sprintf(sql,"create table userfriend%d(friend text,friendid integer);",num);
    if (sqlite3_exec(db, sql, NULL, NULL, NULL) != SQLITE_OK)
    {
        printf("create table error %s\n", sqlite3_errmsg(db));
        
        sqlite3_close(db);
        return;
    }
    memset(sql,0,200);
    sprintf(sql,"create table usernews%d(key integer primary key autoincrement,newsflag integer,friendname text,friendid integer,groupflag integer,groupname text,groupid integer,news text);",num);
    if (sqlite3_exec(db, sql, NULL, NULL, NULL) != SQLITE_OK)
    {
        printf("create table error %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(sql,0,200);
    sprintf(sql,"create table usergroup%d(key integer primary key autoincrement,groupname text,groupid integer,mynameid integer,groupflag integer);",num);
    if (sqlite3_exec(db, sql, NULL, NULL, NULL) != SQLITE_OK)
    {
        printf("create table error %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    sqlite3_close(db);
    m->operationflag=112;
}
//11 登录账户
void ServerAdminLogin(Msg *m,DLList *list)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    Node *TravelPoint=list->head;
    while(TravelPoint!=NULL)
    {
        if(((C *)(TravelPoint->data))->mynameid==m->mynameid)
        {
            memset(m,0,sizeof(Msg));
            m->operationflag=1110;
            sqlite3_close(db);
            return ;
        }
        TravelPoint=TravelPoint->next;
    }
    char sql[100];
    sprintf(sql,"select serial from users where mynameid=%d;",m->mynameid);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;

    }
    if(result[row]==NULL)
    {
        sqlite3_close(db);
        return;
    }
    int num=atoi(result[row]);
    sqlite3_free_table(result);

    memset(sql,0,100);
    sprintf(sql,"select password from users where serial=%d;",num);

    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(result[row]==NULL)
    {
        sqlite3_close(db);
        return;
    }
    if(strcmp(result[row],m->password)==0)
    {
        memset(sql,0,100);
        sprintf(sql,"select myname from users where serial=%d;",num);
        sqlite3_free_table(result);
        if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
        {
            printf("get table error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        if(result[row]==NULL)
        {
            sqlite3_close(db);
            return;
        }
        strcpy(m->myname,result[row]);
        m->operationflag=111;
        m->serial=num;
        sqlite3_free_table(result);
        sqlite3_close(db);
        return;
    }
    else
    {
        sqlite3_free_table(result);
        sqlite3_close(db);
        return;
    }
    sqlite3_close(db);
}

void ServerUserLoginRegistration(int clinetsock, Msg *m,DLList *list)
{
    while(1)
    {
        TcpServerRecv(clinetsock,m,sizeof(Msg));
        if(m->operationflag==11)
        {
            ServerAdminLogin(m,list);
            TcpServerSend(clinetsock,m,sizeof(Msg));
            if(m->operationflag==111)
            {
                break;
            }
        }
        if(m->operationflag==12)
        {
            ServerAdminRegister(m);
            TcpServerSend(clinetsock,m,sizeof(Msg));
        }
    }
}



//21 查看全部好友

void AllFriends(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    char sql[100];
    sprintf(sql,"select friend,friendid from userfriend%d;",m->serial);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    m->operationflag=121;
    m->numflag=row;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    for(int i=1;i<=row;i++)
    {
        strcpy(m->toname,result[i*column]);
        m->tonameid=atoi(result[i*column+1]);
        TcpServerSend(clinetsock,m,sizeof(Msg));
        memset(m,0,sizeof(Msg));
    }
    sqlite3_free_table(result);
    sqlite3_close(db);
}
//22 查看在线好友
void OnlineFriends(int clinetsock,Msg *m,DLList *list)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    char sql[100];
    sprintf(sql,"select friend,friendid from userfriend%d;",m->serial);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    m->operationflag=122;
    int count=0;
    for(int i=1;i<=row;i++)
    {
        int temp=atoi(result[i*column+1]);
        Node *TravelPoint=list->head;
        while(TravelPoint!=NULL)
        {
            if(((C *)(TravelPoint->data))->mynameid==temp&&((C *)(TravelPoint->data))->onlineflag==1)
            {
                count++;
                break;
            }
            TravelPoint=TravelPoint->next;
        }
    }
    m->numflag=count;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    memset(m,0,sizeof(Msg));
    for(int i=1;i<=row;i++)
    {
        int temp=atoi(result[i*column+1]);
        Node *TravelPoint=list->head;
        while(TravelPoint!=NULL)
        {
            if(((C *)(TravelPoint->data))->mynameid==temp)
            {
                strcpy(m->toname,result[i*column]);
                m->tonameid=atoi(result[i*column+1]);
                TcpServerSend(clinetsock,m,sizeof(Msg));
                memset(m,0,sizeof(Msg));
                break;
            }
            TravelPoint=TravelPoint->next;
        }
    }
    sqlite3_free_table(result);
    sqlite3_close(db);
}

//23 发消息
void SendFriends(int clinetsock,Msg *m,DLList *list)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[4086]={0};
    sprintf(sql,"select friendid from userfriend%d where friendid=%d;",m->serial,m->tonameid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(result[row]==NULL)
    {
        sqlite3_close(db);
        return;
    }
    if(row==0)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1230;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        sqlite3_close(db);
        return;
    }
    sqlite3_free_table(result);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"insert into usernews%d values(NULL,1,'%s',%d,0,NULL,NULL,'%s');",m->tonameid-10000,m->myname,m->mynameid,m->content);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(m,0,sizeof(Msg));
    m->operationflag=123;
    TcpServerSend(clinetsock,m,sizeof(Msg));

    sqlite3_close(db);
}

//24 查看好友申请
void FriendRequest(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[150]={0};
    sprintf(sql,"select friendname,friendid from usernews%d where newsflag=3",m->serial);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(result[row*column]==NULL||result[row*column+1]==NULL)
    {
        sqlite3_close(db);
        return;
    }
    sqlite3_close(db);
    ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    int serial=m->serial;
    m->numflag=row;
    m->operationflag=124;
    int mynameid=m->mynameid;
    char myname[10]={0};
    strcpy(myname,m->myname);
    TcpServerSend(clinetsock,m,sizeof(Msg));
    for(int i=1;i<=row;i++)
    {
        memset(m,0,sizeof(Msg));
        strcpy(m->toname,result[i*column]);
        m->tonameid=atoi(result[i*column+1]);
        // printf("%s %d\n",m->toname,m->tonameid);
        int tonameid=m->tonameid;
        char toname[10]={0};
        strcpy(toname,m->toname);
        TcpServerSend(clinetsock,m,sizeof(Msg));

        TcpServerRecv(clinetsock,m,sizeof(Msg));
        printf("%d\n",m->operationflag);
        char temp[150]={0};
        if(m->operationflag==1241)
        {
            memset(temp,0,sizeof(temp));
            sprintf(temp,"insert into userfriend%d values('%s',%d);",serial,toname,tonameid);
            printf("%s\n",temp);
            if(sqlite3_exec(db,temp,NULL,NULL,NULL)!=SQLITE_OK)
            {
                printf("exec error %s\n",sqlite3_errmsg(db));
                sqlite3_close(db);
                return;
            }
            memset(temp,0,sizeof(temp));
            sprintf(temp,"insert into userfriend%d values('%s',%d);",tonameid-10000,myname,mynameid);
            printf("%s\n",temp);
            if(sqlite3_exec(db,temp,NULL,NULL,NULL)!=SQLITE_OK)
            {
                printf("exec error %s\n",sqlite3_errmsg(db));
                sqlite3_close(db);
                return;
            }
            m->operationflag=12411;
        }
        memset(temp,0,sizeof(temp));
        sprintf(temp,"delete from usernews%d where newsflag=3 and friendid=%d;",serial,tonameid);
        printf("%s\n",temp);
        if(sqlite3_exec(db,temp,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        printf("%d\n",m->operationflag);
        TcpServerSend(clinetsock,m,sizeof(Msg));

    }
    sqlite3_close(db);

}

//25 查看好友消息
void ViewMessages(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[4086]={0};
    sprintf(sql,"select friendname,friendid,news from usernews%d where newsflag=1;",m->serial);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(result[row*column]==NULL||result[row*column+1]==NULL||result[row*column+2]==NULL)
    {
        sqlite3_close(db);
        return;
    }
    int serial=m->serial;
    m->numflag=row;
    m->operationflag=125;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    for(int i=1;i<=row;i++)
    {
        memset(m,0,sizeof(Msg));
        strcpy(m->toname,result[i*column]);
        m->tonameid=atoi(result[i*column+1]);
        strcpy(m->content,result[i*column+2]);
        printf("%s %d %s\n",m->toname,m->tonameid,m->content);
        int tonameid=m->tonameid;
        char toname[10]={0};
        strcpy(toname,m->toname);
        TcpServerSend(clinetsock,m,sizeof(Msg));
        char temp[150]={0};
        sprintf(temp,"delete from usernews%d where newsflag=1 and friendid=%d;",serial,tonameid);
        printf("%s\n",temp);
        if(sqlite3_exec(db,temp,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }

    }
    sqlite3_close(db);
}

//26 添加好友
void AddFriends(int clinetsock,Msg *m,DLList *list)
{

    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    if(m->tonameid==m->mynameid)
    {
            memset(m,0,sizeof(Msg));
            m->operationflag=1260;
            TcpServerSend(clinetsock,m,sizeof(Msg));
            sqlite3_close(db);
            return;
    }
    char sql[150]={0};
    sprintf(sql,"select friendid from userfriend%d where friendid=%d;",m->serial,m->tonameid);
    printf("%s\n",sql);
    char **result1;
    int row1, column1;
    if(sqlite3_get_table(db, sql,&result1,&row1,&column1,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(row1>0)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1261;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result1);
        sqlite3_close(db);
        return;
    }
    sqlite3_free_table(result1);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"select mynameid from users where mynameid=%d;",m->tonameid);
    printf("%s\n",sql);
    char **result2;
    int row2, column2;
    if(sqlite3_get_table(db, sql,&result2,&row2,&column2,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(row2==0)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1262;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result2);
        sqlite3_close(db);
        return;
    }
    sqlite3_free_table(result2);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"select friendid from usernews%d where newsflag=3 and friendname='%s' and friendid=%d;",m->tonameid-10000,m->myname,m->mynameid);
    printf("%s\n",sql);
    char **result3;
    int row3, column3;
    if(sqlite3_get_table(db, sql,&result3,&row3,&column3,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(row3>0)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1263;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result3);
        sqlite3_close(db);
        return;
    }
    sqlite3_free_table(result3);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"select serial,myname from users where mynameid=%d;",m->tonameid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;

    }
    if(result[row*column]==NULL||result[row*column+1]==NULL)
    {
        sqlite3_close(db);
        return;
    }
    int num=atoi(result[row*column]);
    strcpy(m->toname,result[row*column+1]);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"insert into usernews%d values(NULL,3,'%s',%d,0,NULL,NULL,NULL);",num,m->myname,m->mynameid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        
        sqlite3_close(db);
        return;
    }

    m->operationflag=126;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    sqlite3_free_table(result);
    sqlite3_close(db);
}


//27 删除好友
void DeleteFriends(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[150]={0};
    sprintf(sql,"select * from userfriend%d where friendid=%d;",m->serial,m->tonameid);
    char **result1;
    int row1, column1;
    if(sqlite3_get_table(db, sql,&result1,&row1,&column1,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(row1==0)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1271;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result1);
        sqlite3_close(db);
        return;
    }
    sqlite3_free_table(result1);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"delete from userfriend%d where friendid=%d;",m->serial,m->tonameid);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(sql,0,sizeof(sql));
    sprintf(sql,"delete from userfriend%d where friendid=%d;",m->tonameid-10000,m->mynameid);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }


    m->operationflag=127;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    sqlite3_close(db);
}





















//41
void ChangePassword(int clinetsock,Msg *m)
{
    sqlite3 *db;
    char sql[100]={0};
    if(sqlite3_open("user.db",&db) != SQLITE_OK)
    {
        printf("open database error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    sprintf(sql,"update users set password='%s' where serial=%d;",m->password,m->serial);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    m->operationflag=141;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    sqlite3_close(db);
}




void ChangAccountName(int clinetsock,Msg *m)
{
    sqlite3 *db;
    char sql[100]={0};
    if(sqlite3_open("user.db",&db) != SQLITE_OK)
    {
        printf("open database error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    sprintf(sql,"update users set myname='%s' where serial=%d;",m->myname,m->serial);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    m->operationflag=142;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    sqlite3_close(db);

}


void StatusSwitching(int clinetsock,Msg *m,DLList *list)
{
    Node *TravelPoint=list->head;
    while(TravelPoint!=NULL)
    {
        if(((C *)(TravelPoint->data))->mynameid==m->mynameid)
        {
            if(m->operationflag==2)
            {
                ((C *)(TravelPoint->data))->onlineflag=2;
                m->operationflag=1432;
                TcpServerSend(clinetsock,m,sizeof(Msg));
                break;
            }
            if(m->operationflag==1)
            {
                ((C *)(TravelPoint->data))->onlineflag=1;
                m->operationflag=1431;
                TcpServerSend(clinetsock,m,sizeof(Msg));
                break;
            }
        }
        TravelPoint=TravelPoint->next;
    }
    m->operationflag=143;
    TcpServerSend(clinetsock,m,sizeof(Msg));
}

//44
void LogOut(int clinetsock,Msg *m,DLList *list)
{
    Node *TravelPoint=list->head;
    int count=-1;
    while(TravelPoint!=NULL)
    {
        count++;
        if(((C *)(TravelPoint->data))->mynameid==m->mynameid)
        {
            
            break;
        }
        TravelPoint=TravelPoint->next;
    }
    RemoveByIndex(list,count);
    m->operationflag=144;
    TcpServerSend(clinetsock,m,sizeof(Msg));
}
//31
void ViewGroupChat(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[150]={0};
    sprintf(sql,"select groupname,groupid from usergroup%d where mynameid=%d;",m->serial,m->mynameid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;

    }
    if(row==0)
    {
        m->numflag=row;
        sqlite3_free_table(result);
        m->operationflag=131;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_close(db);
        return;
    }
    m->numflag=row;
    m->operationflag=131;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    for(int i=1;i<=row;i++)
    {
        memset(m,0,sizeof(Msg));
        strcpy(m->group,result[i*column]);
        m->groupid=atol(result[i*column+1]);
        TcpServerSend(clinetsock,m,sizeof(Msg));
    }
    sqlite3_free_table(result);
    sqlite3_close(db);
}
//32
void ViewManageGroup(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[150]={0};
    sprintf(sql,"select groupname,groupid from usergroup%d where mynameid=%d and groupflag<3;",m->serial,m->mynameid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;

    }
    if(result[row*column]==NULL||result[row*column+1]==NULL)
    {
        sqlite3_close(db);
        return;
    }
    m->numflag=row;
    printf("%d\n",row);
    m->operationflag=132;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    for(int i=1;i<=row;i++)
    {
        memset(m,0,sizeof(Msg));
        strcpy(m->group,result[i*column]);
        m->groupid=atol(result[i*column+1]);
        printf("%s %d\n",m->toname,m->tonameid);
        TcpServerSend(clinetsock,m,sizeof(Msg));
    }
    sqlite3_free_table(result);
    sqlite3_close(db);
}

//34  
void CreatGroupChat(int clinetsock,Msg *m)
{
    sqlite3 *db;
    char sql[4086]={0};
    int serial=m->serial;
    if(sqlite3_open("user.db",&db) != SQLITE_OK)    
    {
        printf("open database error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    sprintf(sql,"insert into usergroup%d(groupname,mynameid,groupflag) values('%s',%d,1);",m->serial,m->content,m->mynameid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL) != SQLITE_OK)
    {
        printf("insert error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(sql,0,sizeof(sql));
    char **result;
    int row,column;
    sprintf(sql,"select key from usergroup%d;",m->serial);
    if(sqlite3_get_table(db,sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;

    }
    if(result[row]==NULL)
    {
        sqlite3_close(db);
        return;
    }
    int num=atoi(result[row]);
    printf("%d\n",num);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"update usergroup%d set groupid=%d where key=%d;",serial,num+serial*1000,num);
    
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL) != SQLITE_OK)
    {
        printf("update error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
    }
    memset(sql,0,sizeof(sql));
    sprintf(sql,"create table groupid%d(key integer primary key autoincrement,username text,userid integer,groupflag integer,chatflag integer);",num+serial*1000);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL) != SQLITE_OK)
    {
        printf("create table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
    }
    printf("%s\n",sql);

    memset(sql,0,sizeof(sql));
    sprintf(sql,"create table groupdata%d(key integer primary key autoincrement,username text,userid integer,filename text,file blob,filesize integer);",num+serial*1000);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL) != SQLITE_OK)
    {
        printf("create table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
    }
    memset(sql,0,sizeof(sql));
    sprintf(sql,"insert into groupid%d values(NULL,'%s',%d,1,1);",num+serial*1000,m->myname,m->mynameid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL) != SQLITE_OK)
    {
        printf("insert error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
    }
    memset(m,0,sizeof(Msg));
    sqlite3_close(db);
    strcpy(m->content,result[row]);
    m->tonameid=num+serial*1000;
    sqlite3_free_table(result);
    m->operationflag=134;
    TcpServerSend(clinetsock,m,sizeof(Msg));

}


//35
void GroupNotification(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[150]={0};
    sprintf(sql,"select groupname,groupid from usernews%d where newsflag=4",m->serial);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(result[row*column]==NULL||result[row*column+1]==NULL)
    {
        sqlite3_close(db);

        return;
    }
    int serial=m->serial;
    m->numflag=row;
    m->operationflag=135;
    int mynameid=m->mynameid;
    char myname[10]={0};
    strcpy(myname,m->myname);
    TcpServerSend(clinetsock,m,sizeof(Msg));
    for(int i=1;i<=row;i++)
    {
        memset(m,0,sizeof(Msg));
        strcpy(m->group,result[i*column]);
        m->groupid=atoi(result[i*column+1]);
        char group[10]={0};
        strcpy(group,m->group);
        int groupid=m->groupid;
        TcpServerSend(clinetsock,m,sizeof(Msg));

        TcpServerRecv(clinetsock,m,sizeof(Msg));
        printf("%d\n",m->operationflag);
        char temp[150]={0};
        if(m->operationflag==1351)
        {
            memset(temp,0,sizeof(temp));
            sprintf(temp,"insert into usergroup%d values(NULL,'%s',%d,%d,3);",serial,group,groupid,mynameid);
            printf("%s\n",temp);
            if(sqlite3_exec(db,temp,NULL,NULL,NULL)!=SQLITE_OK)
            {
                printf("exec error %s\n",sqlite3_errmsg(db));
                sqlite3_close(db);
                return;
            }
            memset(temp,0,sizeof(temp));
            sprintf(temp,"insert into groupid%d values(NULL,'%s',%d,3,1);",groupid,myname,mynameid);
            printf("%s\n",temp);
            if(sqlite3_exec(db,temp,NULL,NULL,NULL)!=SQLITE_OK)
            {
                printf("exec error %s\n",sqlite3_errmsg(db));
                sqlite3_close(db);
                return;
            }
            m->operationflag=13511;
        }
        memset(temp,0,sizeof(temp));
        sprintf(temp,"delete from usernews%d where newsflag=4 and groupid=%d;",serial,groupid);
        printf("%s\n",temp);
        if(sqlite3_exec(db,temp,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        sqlite3_free_table(result);
        printf("%d\n",m->operationflag);
        TcpServerSend(clinetsock,m,sizeof(Msg));

    }
    sqlite3_close(db);
}

//50
void AccessGroup(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    char sql[150]={0};
    sprintf(sql,"select groupid from usergroup%d where groupid=%d",m->serial,m->groupid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(result[row*column]==NULL)
    {
        sqlite3_close(db);
        return;
    }
    if(row!=1)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1501;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        sqlite3_close(db);
    }
    else
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=150;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        sqlite3_close(db);
    }
}
//51
void ViewGroupMembers(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    char sql[150]={0};
    sprintf(sql,"select username,userid from groupid%d;",m->groupid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(m,0,sizeof(Msg));
    m->operationflag=151;
    m->numflag=row;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    for(int i=1;i<=row;i++)
    {
        memset(m,0,sizeof(Msg));
        strcpy(m->toname,result[i*column]);
        m->tonameid=atoi(result[i*column+1]);
        TcpServerSend(clinetsock,m,sizeof(Msg));
    }
    sqlite3_free_table(result);
    sqlite3_close(db);
}
//52 
void GroupChat(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[4086]={0};
    sprintf(sql,"select groupname from usergroup%d where mynameid=%d;",m->serial,m->mynameid);
    printf("%s\n",sql);
    char **result1;
    int row1, column1;
    if(sqlite3_get_table(db, sql,&result1,&row1,&column1,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }   
    if(row1==0)
    {
        sqlite3_close(db);
        memset(m,0,sizeof(Msg));
        m->operationflag=152;
        m->numflag=0;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        return;
    }
    char group[10]={0};
    strcpy(group,result1[row1*column1]);
    sqlite3_free_table(result1);
    
    char **result4;
    memset(sql,0,sizeof(sql));
    sprintf(sql,"select chatflag from groupid%d where userid=%d;",m->groupid,m->mynameid);
    printf("%s\n",sql);
    if(sqlite3_get_table(db, sql,&result4,&row1,&column1,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(atoi(result4[row1])==0)
    {
        memset(m,0,sizeof(Msg));
        sqlite3_free_table(result4);
        m->operationflag=1520;
        sqlite3_close(db);
        TcpServerSend(clinetsock,m,sizeof(Msg));
        return;
    }

    memset(sql,0,sizeof(sql));
    sprintf(sql,"select username,userid from groupid%d where userid!=%d;",m->groupid,m->mynameid);
    printf("%s\n",sql);
    char **result2;
    if(sqlite3_get_table(db, sql,&result2,&row1,&column1,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;

    }
    if(result2[row1*column1]==NULL||result2[row1*column1+1]==NULL)
    {
        sqlite3_close(db);
        return;
    }

    for(int i=1;i<=row1;i++)
    {
        char temp[4086]={0};
        memset(temp,0,sizeof(temp));
        sprintf(temp,"insert into usernews%d values(NULL,2,'%s',%d,0,'%s',%d,'%s');",atoi(result2[i*column1+1])-10000,m->myname,m->mynameid,group,m->groupid,m->content);
        printf("%s\n",temp);
        if(sqlite3_exec(db,temp,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
    }
    sqlite3_free_table(result2);
    m->operationflag=152;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    sqlite3_close(db);
}

//53
void ViewGroupNews(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[4086]={0};
    sprintf(sql,"select key,friendname,friendid,news from usernews%d where newsflag=2 and groupid=%d;",m->serial,m->groupid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(result[row*column]==NULL||result[row*column+1]==NULL||result[row*column+2]==NULL||result[row*column+3]==NULL)
    {
        sqlite3_close(db);
        return;
    }
    printf("%d\n",row);
    int serial=m->serial;
    memset(m,0,sizeof(Msg));
    m->numflag=row;
    m->operationflag=153;

    TcpServerSend(clinetsock,m,sizeof(Msg));
    for(int i=1;i<=row;i++)
    {
        memset(m,0,sizeof(Msg));
        int key=atoi(result[i*column]);
        strcpy(m->toname,result[i*column+1]);
        m->tonameid=atoi(result[i*column+2]);
        strcpy(m->content,result[i*column+3]);
        int tonameid=m->tonameid;
        char toname[10]={0};
        strcpy(toname,m->toname);
        TcpServerSend(clinetsock,m,sizeof(Msg));
        char temp[150]={0};
        sprintf(temp,"delete from usernews%d where key=%d;",serial,key);
        printf("%s\n",temp);
        if(sqlite3_exec(db,temp,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }

    }
    sqlite3_close(db);
}
//54
void UploadFiles(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    FMsg file;
    memset(&file,0,sizeof(FMsg));
    TcpServerRecv(clinetsock,&file,sizeof(FMsg));
    char sql[1058048]={0};
    char filename[20];
    char *lastslash=strchr(file.filename,'/');
    char temp[20]={0};
    strcpy(temp,lastslash);
    if(lastslash!=NULL)
    {
        strcpy(filename,lastslash+1);
    }
    sprintf(sql,"insert into groupdata%d values(NULL,'%s',%d,'%s','%s',%d);",m->groupid,m->myname,m->mynameid,&temp[1],file.file,file.size);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(m,0,sizeof(Msg));
    m->operationflag=154;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    sqlite3_close(db); 
}
//55
void DownloadFiles(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    int groupid=m->groupid;
    char sql[200]={0};  
    sprintf(sql,"select key,username,userid,filename,filesize from groupdata%d;",m->groupid);
    printf("%s\n",sql);
    char **result;
    int row,column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(m,0,sizeof(Msg));
    m->operationflag=155;
    m->numflag=row;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    for(int i=1;i<=row;i++)
    {
        memset(m,0,sizeof(Msg));
        m->serial=atoi(result[i*column+0]);
        strcpy(m->toname,result[i*column+1]);
        m->tonameid=atoi(result[i*column+2]);
        strcpy(m->filename,result[i*column+3]);
        m->filesize=atoi(result[i*column+4]);
        TcpServerSend(clinetsock,m,sizeof(Msg));
    }
    sqlite3_free_table(result);
    TcpServerRecv(clinetsock,m,sizeof(Msg));
    int key=m->operationflag;
    char **result1;
    memset(sql,0,sizeof(sql));
    sprintf(sql,"select filename,file,filesize from groupdata%d where key=%d;",groupid,key);
    printf("%s\n",sql);
    
    if(sqlite3_get_table(db, sql,&result1,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    FMsg file;
    memset(&file,0,sizeof(FMsg));
    if(row==0)
    {
        sqlite3_close(db);
        sqlite3_free_table(result1);
        file.flag=0;
        // TcpServerSend(clinetsock,&file,sizeof(FMsg));
        return;
    }
    else
    {
        strcpy(file.filename,result1[row*column]);
        strcpy(file.file,result1[row*column+1]);
        file.size=atoi(result1[row*column+2]);
        file.flag=1;
        printf("%s %s %d\n",file.filename,file.file,file.size);
        // TcpServerSend(clinetsock,&file,sizeof(FMsg));
        
        memset(m,0,sizeof(Msg));
        strcpy(m->filename,result1[row*column]);
        strcpy(m->content,result1[row*column+1]);
        m->filesize=atoi(result1[row*column+2]);
        printf("%d %d %s\n",m->operationflag,m->filesize,m->filename);
        printf("%s\n",m->content);
        m->operationflag=1551;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result1);
        sqlite3_close(db);
    }
}
//56
void BannedToPost(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[4086]={0};
    sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->mynameid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(atoi(result[row])==1) 
    {
        memset(sql,0,sizeof(sql));
        sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->tonameid);
        printf("%s\n",sql);
        char **result1;
        if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
        {
            printf("get table error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        if(row==0)
        {
            memset(m,0,sizeof(Msg));
            m->operationflag=1561;
            TcpServerSend(clinetsock,m,sizeof(Msg));
            sqlite3_free_table(result);
            return;
        }
        memset(sql,0,sizeof(sql));
        sprintf(sql,"update groupid%d set chatflag=0 where userid=%d;",m->groupid,m->tonameid);
        if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        memset(m,0,sizeof(Msg));
        m->operationflag=156;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        return;
    }
    if(atoi(result[row])==2)
    {
        memset(sql,0,sizeof(sql));
        sprintf(sql,"select groupflag  from groupid%d where userid=%d;",m->groupid,m->tonameid);
        char **result1;
        if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
        {
            printf("get table error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        if(row==0)
        {
            memset(m,0,sizeof(Msg));
            m->operationflag=1561;
            TcpServerSend(clinetsock,m,sizeof(Msg));
            sqlite3_free_table(result);
            return;
        }
        if(atoi(result[row])==1||atoi(result[row])==2)
        {
            memset(m,0,sizeof(Msg));
            m->operationflag=1562;
            TcpServerSend(clinetsock,m,sizeof(Msg));
            sqlite3_free_table(result);
            return;
        }
        memset(sql,0,sizeof(sql));
        sprintf(sql,"update groupid%d set chatflag=0 where userid=%d;",m->groupid,m->tonameid);
        if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        
        memset(m,0,sizeof(Msg));
        m->operationflag=156;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        sqlite3_close(db);
        return;
    }
    if(atoi(result[row])==3)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1563;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        sqlite3_close(db);
        return;
    }
}
//57
void LiftTheGag(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
        char sql[4086]={0};
    sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->mynameid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(atoi(result[row])==1)
    {
        memset(sql,0,sizeof(sql));
        sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->tonameid);
        char **result1;
        if(sqlite3_get_table(db, sql,&result1,&row,&column,NULL) != SQLITE_OK)
        {
            printf("get table error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        if(row==0)
        {
            memset(m,0,sizeof(Msg));
            m->operationflag=1571;
            TcpServerSend(clinetsock,m,sizeof(Msg));
            sqlite3_free_table(result);
            sqlite3_free_table(result1);
            return;
        }
        memset(sql,0,sizeof(sql));
        sprintf(sql,"update groupid%d set chatflag=1 where userid=%d;",m->groupid,m->tonameid);
        if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        memset(m,0,sizeof(Msg));
        m->operationflag=157;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        sqlite3_free_table(result1);
        return;
    }
    if(atoi(result[row])==2)
    {
        memset(sql,0,sizeof(sql));
        sprintf(sql,"select groupflag  from groupid%d where userid=%d;",m->groupid,m->tonameid);
        char **result2;
        if(sqlite3_get_table(db, sql,&result2,&row,&column,NULL) != SQLITE_OK)
        {
            printf("get table error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        if(row==0)
        {
            memset(m,0,sizeof(Msg));
            m->operationflag=1571;
            TcpServerSend(clinetsock,m,sizeof(Msg));
            sqlite3_free_table(result);
            sqlite3_free_table(result2);
            return;
        }
        if(atoi(result[row])==1||atoi(result[row])==2)
        {
            memset(m,0,sizeof(Msg));
            m->operationflag=1572;
            TcpServerSend(clinetsock,m,sizeof(Msg));
            sqlite3_free_table(result);
            sqlite3_free_table(result2);
            return;
        }
        memset(sql,0,sizeof(sql));
        sprintf(sql,"update groupid%d set chatflag=0 where userid=%d;",m->groupid,m->tonameid);
        if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        memset(m,0,sizeof(Msg));
        m->operationflag=157;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        sqlite3_free_table(result2);
        return;
    }
    if(atoi(result[row])==3)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1573;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        return;
    }
}

//58
void AddUser(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[150]={0};
    sprintf(sql,"select friend from userfriend%d where friendid=%d;",m->serial,m->tonameid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;

    }
    if(row==0)
    {
        sqlite3_free_table(result);

        sqlite3_close(db);
        memset(m,0,sizeof(Msg));
        m->operationflag=1581;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        return;
    }
    int num=m->tonameid-10000;
    strcpy(m->toname,result[row]);
    sqlite3_free_table(result);

    memset(sql,0,sizeof(sql));
    sprintf(sql,"select groupname from usergroup%d where groupid=%d;",m->serial,m->groupid);
    printf("%s\n",sql);
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;

    }
    if(result[row*column]==NULL||result[row*column+1]==NULL)
    {
        sqlite3_close(db);
        return;
    }
    memset(sql,0,sizeof(sql));
    sprintf(sql,"insert into usernews%d values(NULL,4,'%s',%d,0,'%s',%d,NULL);",num,m->myname,m->mynameid,result[row],m->groupid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    m->operationflag=158;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    sqlite3_free_table(result);
    sqlite3_close(db);
}
//59
void AdministratorSettings(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[150]={0};
    sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->mynameid);
    printf("%s\n",sql);
    char **result;
    int row,column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(atoi(result[row])!=1)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1591;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        sqlite3_close(db);
        return;
    }
    sqlite3_free_table(result);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->tonameid);
    printf("%s\n",sql);
    char **result1;
    if(sqlite3_get_table(db, sql,&result1,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(row==0)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1592;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result1);
        return;
    }
    sqlite3_free_table(result1);



    memset(sql,0,sizeof(sql));
    sprintf(sql,"update groupid%d set groupflag=2 where userid=%d;",m->groupid,m->tonameid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(sql,0,sizeof(sql));
    sprintf(sql,"update usergroup%d set groupflag=2 where groupid=%d;",m->tonameid-10000,m->groupid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(m,0,sizeof(Msg));
    m->operationflag=159;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    sqlite3_close(db);

}
//60
void DisbandGroup(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    char sql[150]={0};
    sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->mynameid);
    printf("%s\n",sql);
    char **result;
    int row,column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(atoi(result[row])!=1)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1600;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_close(db);
        sqlite3_free_table(result);
        return;
    }
    sqlite3_free_table(result);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"select userid from groupid%d;",m->groupid);
    printf("%s\n",sql);
    char **result1;
    if(sqlite3_get_table(db, sql,&result1,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    for(int i=1;i<=row;i++)
    {
        memset(sql,0,sizeof(sql));
        sprintf(sql,"delete from usergroup%d where groupid=%d;",atoi(result1[i])-10000,m->groupid);
        printf("%s\n",sql);
        if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
    }
    sqlite3_free_table(result1);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"drop table groupid%d;",m->groupid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(sql,0,sizeof(sql));
    sprintf(sql,"drop table groupdata%d;",m->groupid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    m->operationflag=160;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    sqlite3_close(db);
}
//61
void LeaveGroup(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[150]={0};
    sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->mynameid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }

    if(atoi(result[row])==1)
    {
        sqlite3_free_table(result);
        sqlite3_close(db);
        memset(m,0,sizeof(Msg));
        m->operationflag=1610;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        return;
    }
    memset(sql,0,sizeof(sql));
    sprintf(sql,"delete from groupid%d where username='%s' and userid=%d;",m->groupid,m->myname,m->mynameid );
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(sql,0,sizeof(sql));
    sprintf(sql,"delete from usergroup%d where groupid=%d;",m->serial,m->groupid );
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(m,0,sizeof(Msg));
    m->operationflag=161;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    sqlite3_close(db);
}
//62
void GroupTransfer(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    } 
    char sql[150]={0};
    sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->mynameid);
    printf("%s\n",sql);
    char **result;
    int row,column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(atoi(result[row])!=1)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1621;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        sqlite3_close(db);
        return;
    }
    sqlite3_free_table(result);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->tonameid);
    printf("%s\n",sql);
    char **result1;
    if(sqlite3_get_table(db, sql,&result1,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(row==0)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1622;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result1);
        return;
    }
    sqlite3_free_table(result1);
    memset(sql,0,sizeof(sql));
    sprintf(sql,"update groupid%d set groupflag=1 where userid=%d;",m->groupid,m->tonameid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(sql,0,sizeof(sql));
    sprintf(sql,"update usergroup%d set groupflag=1 where groupid=%d;",m->tonameid-10000,m->groupid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(sql,0,sizeof(sql));

    sprintf(sql,"update groupid%d set groupflag=3 where userid=%d;",m->groupid,m->mynameid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(sql,0,sizeof(sql));
    sprintf(sql,"update usergroup%d set groupflag=3 where groupid=%d;",m->serial,m->groupid);
    printf("%s\n",sql);
    if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
    {
        printf("exec error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    memset(m,0,sizeof(Msg));
    m->operationflag=162;
    TcpServerSend(clinetsock,m,sizeof(Msg));
    sqlite3_close(db);
}

//63
void RemoveGroup(int clinetsock,Msg *m)
{
    sqlite3 *db;
    int ret=sqlite3_open("user.db",&db);
    if(ret!=SQLITE_OK)
    {
        printf("open error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
        char sql[4086]={0};
    sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->mynameid);
    printf("%s\n",sql);
    char **result;
    int row, column;
    if(sqlite3_get_table(db, sql,&result,&row,&column,NULL) != SQLITE_OK)
    {
        printf("get table error %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
    if(atoi(result[row])==1)
    {
        memset(sql,0,sizeof(sql));
        sprintf(sql,"select  groupflag from groupid%d where userid=%d;",m->groupid,m->tonameid);
        printf("%s\n",sql);
        char **result1;
        if(sqlite3_get_table(db, sql,&result1,&row,&column,NULL) != SQLITE_OK)
        {
            printf("get table error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        if(row==0)
        {
            memset(m,0,sizeof(Msg));
            m->operationflag=1631;
            TcpServerSend(clinetsock,m,sizeof(Msg));
            sqlite3_free_table(result);
            sqlite3_free_table(result1);
            return;
        }
        memset(sql,0,sizeof(sql));
        sprintf(sql,"delete from groupid%d where userid=%d;",m->groupid,m->tonameid);
        printf("%s\n",sql);
        if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        memset(sql,0,sizeof(sql));
        sprintf(sql,"delete from usergroup%d where groupid=%d;",m->tonameid-10000,m->groupid);
        printf("%s\n",sql);
        if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        memset(m,0,sizeof(Msg));
        m->operationflag=163;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        sqlite3_free_table(result1);
        return;
    }
    if(atoi(result[row])==2)
    {
        memset(sql,0,sizeof(sql));
        sprintf(sql,"select groupflag  from groupid%d where userid=%d;",m->groupid,m->tonameid);
        char **result1;
        if(sqlite3_get_table(db, sql,&result1,&row,&column,NULL) != SQLITE_OK)
        {
            printf("get table error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        if(row==0)
        {
            memset(m,0,sizeof(Msg));
            m->operationflag=1631;
            TcpServerSend(clinetsock,m,sizeof(Msg));
            sqlite3_free_table(result);
            sqlite3_free_table(result1);
            return;
        }
        if(atoi(result[row])==1||atoi(result[row])==2)
        {
            memset(m,0,sizeof(Msg));
            m->operationflag=1632;
            TcpServerSend(clinetsock,m,sizeof(Msg));
            sqlite3_free_table(result);
            sqlite3_free_table(result1);
            return;
        }
        memset(sql,0,sizeof(sql));
        sprintf(sql,"delete from groupid&%d where userid=%d;",m->groupid,m->tonameid);
        if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        memset(sql,0,sizeof(sql));
        sprintf(sql,"delete from usergroup%d where groupid=%d;",m->tonameid-10000,m->groupid);
        if(sqlite3_exec(db,sql,NULL,NULL,NULL)!=SQLITE_OK)
        {
            printf("exec error %s\n",sqlite3_errmsg(db));
            sqlite3_close(db);
            return;
        }
        memset(m,0,sizeof(Msg));
        m->operationflag=163;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        sqlite3_free_table(result1);
        return;
    }
    if(atoi(result[row])==3)
    {
        memset(m,0,sizeof(Msg));
        m->operationflag=1633;
        TcpServerSend(clinetsock,m,sizeof(Msg));
        sqlite3_free_table(result);
        return;
    }
}
void ServerAdminOperation(int clinetsock,Msg *m,DLList *list)
{
    while(1)
    {
        TcpServerRecv(clinetsock,m,sizeof(Msg));
        switch (m->operationflag)
        {
        case 21:
            AllFriends(clinetsock,m);
            break;
        case 22:
            OnlineFriends(clinetsock,m,list);
            break;
        case 23:
            SendFriends(clinetsock,m,list);
            break;
        case 24:
            FriendRequest(clinetsock,m);
            break;
        case 25:
            ViewMessages(clinetsock,m);
            break;
        case 26:
            AddFriends(clinetsock,m,list);
            break;
        case 27:
            DeleteFriends(clinetsock,m);
            break;
        case 31:
            ViewGroupChat(clinetsock,m);
            break;
        case 32:
            ViewManageGroup(clinetsock,m);
            break;
        case 34:
            CreatGroupChat(clinetsock,m);
            break;
        case 35:
            GroupNotification(clinetsock,m);
            break;
        case 50:
            AccessGroup(clinetsock,m);
            break;
        case 51:
            ViewGroupMembers(clinetsock,m);
            break;
        case 52:
            GroupChat(clinetsock,m);
            break;
        case 53:
            ViewGroupNews(clinetsock,m);
            break;
        case 54:
            UploadFiles(clinetsock,m);
            break;
        case 55:
            DownloadFiles(clinetsock,m);
            break;
        case 56:
            BannedToPost(clinetsock,m);
            break;
        case 57:
            LiftTheGag(clinetsock,m);
            break;
        case 58:
            AddUser(clinetsock,m);
            break;
        case 59:
            AdministratorSettings(clinetsock,m);
            break;
        case 60:
            DisbandGroup(clinetsock,m);
            break;
        case 61:
            LeaveGroup(clinetsock,m);
            break;
        case 62:
            GroupTransfer(clinetsock,m);
            break;
        case 63:
            RemoveGroup(clinetsock,m);
            break;
        case 41:
            //修改密码
            ChangePassword(clinetsock,m);
            break;
        case 42:
            ChangAccountName(clinetsock,m);
            break;
        case 43:
            StatusSwitching(clinetsock,m,list);
            break;
        case 44:
            LogOut(clinetsock,m,list);
            return;
            break;
        default:
            break;
        }
    }
}